const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');

// Database connection
const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

/**
 * Parse CSV file and extract field names, handling multiple tables per file
 */
function parseCSVFile(filePath) {
  try {
    const content = fs.readFileSync(filePath, 'utf8');
    const lines = content.split('\n').filter(line => line.trim() !== '');
    
    if (lines.length < 3) {
      console.log(`Warning: CSV file ${filePath} has insufficient data`);
      return null;
    }
    
    const tables = [];
    let currentTable = null;
    
    for (let i = 0; i < lines.length; i++) {
      const line = lines[i];
      const columns = line.split(',');
      
      // Check if this line is a table title (starts with "表")
      if (columns[0] && columns[0].replace('﻿', '').trim().startsWith('表')) {
        // Save previous table if exists
        if (currentTable && currentTable.fieldNames.length > 0) {
          tables.push(currentTable);
        }
        
        // Start new table
        currentTable = {
          filename: path.basename(filePath),
          tableTitle: columns[0].replace('﻿', '').trim(),
          fieldNames: [],
          totalFields: 0
        };
        
        // Skip the header line (next line usually contains "序号,中文名称,...")
        i++;
        continue;
      }
      
      // If we have a current table and this looks like a data row
      if (currentTable && columns.length >= 2 && columns[1] && columns[1].trim() !== '' && columns[1].trim() !== '中文名称') {
        currentTable.fieldNames.push(columns[1].trim());
      }
    }
    
    // Add the last table
    if (currentTable && currentTable.fieldNames.length > 0) {
      tables.push(currentTable);
    }
    
    // Update total fields count
    tables.forEach(table => {
      table.totalFields = table.fieldNames.length;
    });
    
    return tables.length > 0 ? tables : null;
  } catch (error) {
    console.error(`Error parsing CSV file ${filePath}:`, error.message);
    return null;
  }
}

/**
 * Get all table_c tables from database with their columns
 */
function getDatabaseTableInfo() {
  try {
    const tables = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE 'table_c%'
      ORDER BY name
    `).all();
    
    const tableInfo = [];
    
    for (const table of tables) {
      const columns = db.prepare(`PRAGMA table_info(${table.name})`).all();
      const fieldColumns = columns
        .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name))
        .map(col => col.name);
      
      tableInfo.push({
        tableName: table.name,
        tableCode: table.name.replace('table_c', 'C'),
        columns: fieldColumns,
        totalColumns: fieldColumns.length
      });
    }
    
    return tableInfo;
  } catch (error) {
    console.error('Error getting database table info:', error.message);
    return [];
  }
}

/**
 * Extract table code from CSV table title
 */
function extractTableCode(tableTitle) {
  // Extract C.X pattern from titles like "表 C.2 NAM002 对象命名表" or "表C.14 BAS008 橇基础参数表"
  const match = tableTitle.match(/表\s*C\.?(\d+)/i);
  return match ? `C${match[1]}` : null;
}

/**
 * Compare field names character by character
 */
function compareFieldNames(excelFields, dbColumns) {
  const results = {
    exactMatches: [],
    missingFromDB: [],
    extraInDB: [],
    characterDifferences: [],
    specialCharacterFields: []
  };
  
  // Check each Excel field against database columns
  for (const excelField of excelFields) {
    const exactMatch = dbColumns.find(dbCol => dbCol === excelField);
    
    if (exactMatch) {
      results.exactMatches.push(excelField);
    } else {
      // Look for close matches with character differences
      const closeMatch = findCloseMatch(excelField, dbColumns);
      
      if (closeMatch) {
        const difference = findExactCharacterDifference(excelField, closeMatch.column);
        results.characterDifferences.push({
          excel: excelField,
          database: closeMatch.column,
          difference: difference
        });
      } else {
        results.missingFromDB.push(excelField);
      }
    }
    
    // Analyze special characters
    if (hasSpecialCharacters(excelField)) {
      results.specialCharacterFields.push(analyzeSpecialCharacters(excelField));
    }
  }
  
  // Check for extra fields in database
  for (const dbCol of dbColumns) {
    const foundInExcel = excelFields.find(excelField => 
      excelField === dbCol || isCloseMatch(excelField, dbCol)
    );
    
    if (!foundInExcel) {
      results.extraInDB.push(dbCol);
    }
  }
  
  return results;
}

/**
 * Find close match considering common variations
 */
function findCloseMatch(excelField, dbColumns) {
  for (const dbCol of dbColumns) {
    if (isCloseMatch(excelField, dbCol)) {
      return { column: dbCol, type: 'close' };
    }
  }
  return null;
}

/**
 * Check if fields are close matches
 */
function isCloseMatch(field1, field2) {
  // Common variations
  const variations = [
    // Chinese parentheses to English
    [/（/g, '('],
    [/）/g, ')'],
    // Chinese slash to English
    [/／/g, '/'],
    // Full-width space to regular space
    [/　/g, ' '],
    // Multiple spaces to single space
    [/\\s+/g, ' '],
    // Underscores variations
    [/___/g, '/'],
    [/__/g, '_'],
  ];
  
  let variant1 = field1.trim();
  let variant2 = field2.trim();
  
  // Apply all variations
  for (const [from, to] of variations) {
    variant1 = variant1.replace(from, to);
    variant2 = variant2.replace(from, to);
  }
  
  return variant1 === variant2;
}

/**
 * Find exact character differences between two strings
 */
function findExactCharacterDifference(str1, str2) {
  const differences = [];
  const maxLength = Math.max(str1.length, str2.length);
  
  for (let i = 0; i < maxLength; i++) {
    const char1 = str1[i] || '';
    const char2 = str2[i] || '';
    
    if (char1 !== char2) {
      differences.push({
        position: i,
        excel: char1,
        database: char2,
        excelCode: char1 ? char1.charCodeAt(0) : 'missing',
        databaseCode: char2 ? char2.charCodeAt(0) : 'missing'
      });
    }
  }
  
  return {
    differences,
    lengthDifference: str1.length - str2.length,
    summary: differences.length > 0 ? 
      `${differences.length} character(s) differ` : 
      'Identical strings'
  };
}

/**
 * Check if field has special characters
 */
function hasSpecialCharacters(field) {
  const specialChars = /[／/（）()，。：；！？""''\\s]/;
  return specialChars.test(field);
}

/**
 * Analyze special characters in field
 */
function analyzeSpecialCharacters(field) {
  const analysis = {
    field: field,
    hasSlash: /[／/]/.test(field),
    hasParentheses: /[（）()]/.test(field),
    hasChinesePunctuation: /[，。：；！？""'']/.test(field),
    hasSpaces: /\\s/.test(field),
    specialCharacters: [],
    characterCodes: []
  };
  
  // Extract all non-standard characters
  for (let i = 0; i < field.length; i++) {
    const char = field[i];
    const code = char.charCodeAt(0);
    
    // Check for special characters (not alphanumeric, not common Chinese)
    if (!/[a-zA-Z0-9\\u4e00-\\u9fff]/.test(char)) {
      analysis.specialCharacters.push(char);
      analysis.characterCodes.push({ char, code, position: i });
    }
  }
  
  return analysis;
}

/**
 * Get CSV files from the excel directory
 */
function getCSVFiles() {
  const csvFiles = [];
  const excelDir = path.join(process.cwd(), 'excel', '06-21');
  
  if (fs.existsSync(excelDir)) {
    const files = fs.readdirSync(excelDir)
      .filter(f => f.endsWith('.csv'))
      .map(f => path.join(excelDir, f));
    csvFiles.push(...files);
  }
  
  return csvFiles;
}

/**
 * Main verification function
 */
function runExactFieldVerification() {
  console.log('🔍 EXACT FIELD MAPPING VERIFICATION');
  console.log('='.repeat(80));
  console.log('Checking character-by-character field name preservation between Excel and Database');
  console.log('='.repeat(80));
  
  const results = [];
  const allSpecialCharacterAnalysis = [];
  
  try {
    // Get database table information
    const dbTables = getDatabaseTableInfo();
    console.log(`📊 Found ${dbTables.length} database tables`);
    
    // Get CSV files
    const csvFiles = getCSVFiles();
    console.log(`📁 Found ${csvFiles.length} CSV files`);
    
    if (csvFiles.length === 0) {
      console.log('❌ No CSV files found in excel/06-21 directory');
      return false;
    }
    
    // Process each CSV file
    for (const csvFile of csvFiles) {
      console.log(`\\n📄 Processing: ${path.basename(csvFile)}`);
      
      const csvTables = parseCSVFile(csvFile);
      if (!csvTables) {
        console.log(`   ❌ Failed to parse CSV file`);
        continue;
      }
      
      console.log(`   📁 Found ${csvTables.length} table(s) in CSV file`);
      
      // Process each table in the CSV file
      for (const csvData of csvTables) {
        console.log(`\\n   📋 Table: ${csvData.tableTitle}`);
        console.log(`   📝 Found ${csvData.fieldNames.length} field names`);
        
        // Extract table code
        const tableCode = extractTableCode(csvData.tableTitle);
        if (!tableCode) {
          console.log(`   ⚠️  Could not extract table code from title: "${csvData.tableTitle}"`);
          continue;
        }
        
        console.log(`   🔍 Extracted table code: ${tableCode}`);
        
        // Find corresponding database table
        let dbTable = dbTables.find(table => table.tableCode === tableCode);
        if (!dbTable) {
          console.log(`   ❌ No matching database table found for ${tableCode}`);
          // Try to find by number only
          const tableNumber = tableCode.replace('C', '');
          const dbTableByNumber = dbTables.find(table => 
            table.tableName === `table_c${tableNumber}`
          );
          if (dbTableByNumber) {
            console.log(`   🔗 Found by number: ${dbTableByNumber.tableName}`);
            dbTable = dbTableByNumber;
          } else {
            continue;
          }
        } else {
          console.log(`   🔗 Matching database table: ${dbTable.tableName} (${dbTable.totalColumns} columns)`);
        }
        
        // Compare field names
        const comparison = compareFieldNames(csvData.fieldNames, dbTable.columns);
        
        // Calculate match statistics
        const totalExcelFields = csvData.fieldNames.length;
        const exactMatches = comparison.exactMatches.length;
        const matchPercentage = totalExcelFields > 0 ? (exactMatches / totalExcelFields) * 100 : 0;
        
        const result = {
          csvFile: path.basename(csvFile),
          tableTitle: csvData.tableTitle,
          tableCode: tableCode,
          databaseTable: dbTable.tableName,
          totalExcelFields,
          totalDatabaseFields: dbTable.totalColumns,
          exactMatches: comparison.exactMatches.length,
          missingFromDB: comparison.missingFromDB.length,
          extraInDB: comparison.extraInDB.length,
          characterDifferences: comparison.characterDifferences.length,
          matchPercentage: Math.round(matchPercentage * 100) / 100,
          details: comparison
        };
        
        results.push(result);
        allSpecialCharacterAnalysis.push(...comparison.specialCharacterFields);
        
        // Print immediate results
        console.log(`   📊 Results:`);
        console.log(`      ✅ Exact matches: ${comparison.exactMatches.length}/${totalExcelFields} (${result.matchPercentage}%)`);
        
        if (comparison.missingFromDB.length > 0) {
          console.log(`      ❌ Missing from DB: ${comparison.missingFromDB.length}`);
          comparison.missingFromDB.forEach(field => {
            console.log(`         • "${field}"`);
          });
        }
        
        if (comparison.extraInDB.length > 0) {
          console.log(`      ⚠️  Extra in DB: ${comparison.extraInDB.length}`);
          comparison.extraInDB.forEach(field => {
            console.log(`         • "${field}"`);
          });
        }
        
        if (comparison.characterDifferences.length > 0) {
          console.log(`      🔍 Character differences: ${comparison.characterDifferences.length}`);
          comparison.characterDifferences.forEach(diff => {
            console.log(`         Excel: "${diff.excel}"`);
            console.log(`         Database: "${diff.database}"`);
            console.log(`         Differences: ${diff.difference.summary}`);
            if (diff.difference.differences.length > 0) {
              diff.difference.differences.forEach(d => {
                console.log(`           Position ${d.position}: '${d.excel}' (${d.excelCode}) vs '${d.database}' (${d.databaseCode})`);
              });
            }
            console.log('');
          });
        }
      }
    }
    
    // Generate comprehensive summary
    console.log('\\n' + '='.repeat(80));
    console.log('📋 COMPREHENSIVE VERIFICATION RESULTS');
    console.log('='.repeat(80));
    
    const totalTables = results.length;
    const perfectMatches = results.filter(r => r.matchPercentage === 100 && 
      r.missingFromDB === 0 && r.extraInDB === 0 && r.characterDifferences === 0).length;
    const partialMatches = results.filter(r => r.matchPercentage > 0 && r.matchPercentage < 100).length;
    const noMatches = results.filter(r => r.matchPercentage === 0).length;
    
    const avgMatchPercentage = results.length > 0 ? 
      results.reduce((sum, r) => sum + r.matchPercentage, 0) / results.length : 0;
    
    console.log(`\\n📊 SUMMARY:`);
    console.log(`   Total Tables Verified: ${totalTables}`);
    console.log(`   Perfect Matches: ${perfectMatches} (${((perfectMatches / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Partial Matches: ${partialMatches} (${((partialMatches / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   No Matches: ${noMatches} (${((noMatches / totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Average Match Percentage: ${avgMatchPercentage.toFixed(2)}%`);
    
    // Special character analysis
    if (allSpecialCharacterAnalysis.length > 0) {
      console.log('\\n🔤 SPECIAL CHARACTER ANALYSIS:');
      
      const fieldsWithSlashes = allSpecialCharacterAnalysis.filter(a => a.hasSlash);
      if (fieldsWithSlashes.length > 0) {
        console.log(`\\n   Fields with slashes (${fieldsWithSlashes.length}):`);
        fieldsWithSlashes.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
          analysis.characterCodes.forEach(cc => {
            if (/[／/]/.test(cc.char)) {
              console.log(`       Position ${cc.position}: '${cc.char}' (Unicode: ${cc.code})`);
            }
          });
        });
      }
      
      const fieldsWithParentheses = allSpecialCharacterAnalysis.filter(a => a.hasParentheses);
      if (fieldsWithParentheses.length > 0) {
        console.log(`\\n   Fields with parentheses (${fieldsWithParentheses.length}):`);
        fieldsWithParentheses.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
          analysis.characterCodes.forEach(cc => {
            if (/[（）()]/.test(cc.char)) {
              console.log(`       Position ${cc.position}: '${cc.char}' (Unicode: ${cc.code})`);
            }
          });
        });
      }
      
      const fieldsWithChinesePunctuation = allSpecialCharacterAnalysis.filter(a => a.hasChinesePunctuation);
      if (fieldsWithChinesePunctuation.length > 0) {
        console.log(`\\n   Fields with Chinese punctuation (${fieldsWithChinesePunctuation.length}):`);
        fieldsWithChinesePunctuation.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
          analysis.characterCodes.forEach(cc => {
            if (/[，。：；！？""'']/.test(cc.char)) {
              console.log(`       Position ${cc.position}: '${cc.char}' (Unicode: ${cc.code})`);
            }
          });
        });
      }
    }
    
    // Critical issues check
    const criticalIssues = results.filter(r => 
      r.matchPercentage < 90 || r.characterDifferences > 0
    );
    
    if (criticalIssues.length > 0) {
      console.log(`\\n⚠️  ATTENTION REQUIRED:`);
      console.log(`   ${criticalIssues.length} table(s) have field mapping issues that need attention:`);
      
      criticalIssues.forEach(issue => {
        console.log(`\\n   📋 ${issue.tableTitle}:`);
        console.log(`      Match rate: ${issue.matchPercentage}%`);
        if (issue.missingFromDB > 0) {
          console.log(`      Missing from database: ${issue.missingFromDB} fields`);
        }
        if (issue.extraInDB > 0) {
          console.log(`      Extra in database: ${issue.extraInDB} fields`);
        }
        if (issue.characterDifferences > 0) {
          console.log(`      Character differences: ${issue.characterDifferences} fields`);
        }
      });
    }
    
    // Save detailed report
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const reportPath = path.join(process.cwd(), `exact-field-verification-${timestamp}.json`);
    
    const report = {
      timestamp: new Date().toISOString(),
      summary: {
        totalTables,
        perfectMatches,
        partialMatches,
        noMatches,
        avgMatchPercentage
      },
      results,
      specialCharacterAnalysis: allSpecialCharacterAnalysis,
      conclusion: criticalIssues.length === 0 ? 
        'All field mappings are preserved correctly' : 
        `${criticalIssues.length} tables require attention for field mapping issues`
    };
    
    fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));
    console.log(`\\n📊 Detailed report saved to: ${reportPath}`);
    
    console.log('\\n' + '='.repeat(80));
    if (criticalIssues.length === 0) {
      console.log('✅ VERIFICATION PASSED: All field names are preserved exactly');
    } else {
      console.log('⚠️  VERIFICATION REQUIRES ATTENTION: Some field mapping issues found');
    }
    console.log('='.repeat(80));
    
    return criticalIssues.length === 0;
    
  } catch (error) {
    console.error('💥 Error during verification:', error);
    return false;
  } finally {
    db.close();
  }
}

// Run verification
if (require.main === module) {
  const success = runExactFieldVerification();
  process.exit(success ? 0 : 1);
}

module.exports = {
  runExactFieldVerification,
  compareFieldNames,
  analyzeSpecialCharacters,
  findExactCharacterDifference
};